package com.orangelife.utils;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletResponse;
import javax.ws.rs.WebApplicationException;
import javax.ws.rs.core.StreamingOutput;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;

import com.orangelife.exception.AppBizException;

/**
 * excel导出
 * 
 * @author zhr
 * 
 */
public class DownExcel {
	
	public static void main(String[] args) throws AppBizException {
		
		

	}
	
	/**
	 * excel导出
	 * 
	 * @param info 表头
	 * @param list 列表集合
	 * @return
	 * @throws AppBizException 
	 */
	public StreamingOutput downExcel(final HttpServletResponse servletResponse,String info,final List<Map<String, Object>> list) throws AppBizException {
		SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
		final String filename = df.format(new Date());
		StreamingOutput stream = null;

		if (StringUtils.isBlank(info)) {

			throw new AppBizException(000101, "表头标题不可为空");
		}
		final String infos[] = info.split(",");
		stream = new StreamingOutput() {
			public void write(OutputStream output) throws IOException,
					WebApplicationException {
				try {

					servletResponse.setContentType("application/vnd.ms-excel");
					servletResponse.setHeader(
							"Content-Disposition",
							"attachment; filename="
									+ new String(("excel表" +filename + ".xls")
											.getBytes("UTF-8"), "iso8859-1"));
					servletResponse.setHeader("Pragma", "No-cache");
					servletResponse.setHeader("Cache-Control", "No-cache");
					servletResponse.setDateHeader("Expires", 0); // 这个地方一定要进行编码的转换要不然中文字符会出现乱码.
					// 设置下载头信息.end,
					// SimpleDateFormat df = new
					// SimpleDateFormat("yyyy-MM-dd");

					InputStream fis = null;
					// PrintWriter out = response.getWriter();

					try {
						
						List<Map<String, Object>> conList = list;

						output = servletResponse.getOutputStream();
						jxl.write.WritableWorkbook wwb = Workbook
								.createWorkbook(output);
						jxl.write.WritableSheet ws = wwb.createSheet("Sheet1",
								0); // 设置标题.ws.addCell(new
									// jxl.write.Label(列,
									// //
									// 行,内容.));
						jxl.write.WritableFont wfont = new jxl.write.WritableFont(
								WritableFont.createFont("宋体"), 12);
						WritableCellFormat font = new WritableCellFormat(wfont);
						for (int i = 0; i < infos.length; i++) {
							ws.addCell(new Label(i, 0, infos[i], font));
							ws.setColumnView(i, 40);
						}
						for (int j = 0; j < conList.size(); j++) {
							Map<String, Object> map = conList.get(j);
							@SuppressWarnings("rawtypes")
							Set set = map.entrySet();
							@SuppressWarnings("rawtypes")
							Iterator it = set.iterator();
							int i = 0;
							while (it.hasNext()) {
								@SuppressWarnings("unchecked")
								Map.Entry<String, Object> entry = (Map.Entry<String, Object>) it
										.next();
								System.out.println(entry.getKey() + "=="
										+ entry.getValue());
								if(entry.getValue()!=null){
									ws.addCell(new Label(i, j + 1,
											entry.getValue().toString(), font));
								}else{
									ws.addCell(new Label(i, j + 1,
											" ", font));
								}
								i++;
							}





						}


						wwb.write();
						if (wwb != null) {
							wwb.close();
						}

					} catch (Exception e) {
						e.printStackTrace();
					} finally {// 正常关闭输入输出流.
						if (fis != null) {
							fis.close();
						}
						if (output != null) {
							output.flush();
							output.close();
						}

					}

				} catch (Exception e) {e.printStackTrace();
					throw new WebApplicationException(e);
					
				}
			}
		};
		
		
		
		return stream;

	}
	
	
	/**
	 * excel导出（分Sheet输出）
	 * 
	 * @param info 表头
	 * @param list 列表集合
	 * @param value 单个Sheet限制多少行
	 * @return
	 * @throws AppBizException 
	 */
	public StreamingOutput downExcelPage(final HttpServletResponse servletResponse,String info,final List<Map<String, Object>> list,final int value) throws AppBizException {
		SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
		final String filename = df.format(new Date());
		StreamingOutput stream = null;

		if (StringUtils.isBlank(info)) {

			throw new AppBizException(000101, "表头标题不可为空");
		}
		final String infos[] = info.split(",");
		stream = new StreamingOutput() {
			public void write(OutputStream output) throws IOException,
					WebApplicationException {
				try {

					servletResponse.setContentType("application/vnd.ms-excel");
					servletResponse.setHeader(
							"Content-Disposition",
							"attachment; filename="
									+ new String(("excel表" +filename + ".xls")
											.getBytes("UTF-8"), "iso8859-1"));
					servletResponse.setHeader("Pragma", "No-cache");
					servletResponse.setHeader("Cache-Control", "No-cache");
					servletResponse.setDateHeader("Expires", 0); // 这个地方一定要进行编码的转换要不然中文字符会出现乱码.
					// 设置下载头信息.end,
					// SimpleDateFormat df = new
					// SimpleDateFormat("yyyy-MM-dd");

					InputStream fis = null;
					// PrintWriter out = response.getWriter();

					try {
						
						List<Map<String, Object>> conList = list;

						output = servletResponse.getOutputStream();
						jxl.write.WritableWorkbook wwb = Workbook
								.createWorkbook(output);
						
						jxl.write.WritableFont wfont = new jxl.write.WritableFont(
								WritableFont.createFont("宋体"), 12);
						WritableCellFormat font = new WritableCellFormat(wfont);
						//每页多少行(excel上限65536行)
//						int value = 1500;
						int k = 0;//分sheet的个数
						jxl.write.WritableSheet ws = null;
						for (int j = 0; j < conList.size(); j++) {
							Map<String, Object> map = conList.get(j);
							if( j % value == 0){
								ws = wwb.createSheet("Sheet"+(int)(j/value+1),
										(int)(j/value+1)); // 设置标题.ws.addCell(new
											// jxl.write.Label(列,
											// //
											// 行,内容.));
								
								for (int l = 0; l < infos.length; l++) {
									ws.addCell(new Label(l, 0, infos[l], font));
									ws.setColumnView(l, 40);
								}
								k++;
							}
							
							
							
							@SuppressWarnings("rawtypes")
							Set set = map.entrySet();
							@SuppressWarnings("rawtypes")
							Iterator it = set.iterator();
							int i = 0;
							while (it.hasNext()) {
								
								@SuppressWarnings("unchecked")
								Map.Entry<String, Object> entry = (Map.Entry<String, Object>) it
										.next();
								System.out.println(entry.getKey() + "=="
										+ entry.getValue());
								if(entry.getValue()!=null){
									ws.addCell(new Label(i, j + 1-value*(k-1),
											entry.getValue().toString(), font));
								}else{
									ws.addCell(new Label(i, j + 1-value*(k-1),
											" ", font));
								}
								i++;
							}





						}


						wwb.write();
						if (wwb != null) {
							wwb.close();
						}

					} catch (Exception e) {
						e.printStackTrace();
					} finally {// 正常关闭输入输出流.
						if (fis != null) {
							fis.close();
						}
						if (output != null) {
							output.flush();
							output.close();
						}

					}

				} catch (Exception e) {e.printStackTrace();
					throw new WebApplicationException(e);
					
				}
			}
		};
		
		
		
		return stream;

	}

}